package com.roosterrobotics.replicant.provider;

import android.content.ContentValues;
import android.content.Context;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase;
import android.database.SQLException;
import android.database.Cursor;

import android.util.Log;

public class ReplicantDbAdapter {
	
	
	private SQLiteDatabase database;
	private DatabaseHelper mdbHelper;
	private Context ctx;
	private boolean dbStatus = false;
	
	private static final String LOG_TAG = "ReplicantDbAdapter";
	
	public static final String DATABASE_NAME = "replicant";
	public static final Integer DATABASE_VERSION = 1;
	
	public static final String COMMUNITY_TABLE = "community";
	public static final String COMMUNITY_ID = "_id";
	public static final String COMMUNITY_NAME = "name";
	public static final String CREATE_COMMUNITY_TABLE = "create table community (_id integer primary key autoincrement, "+
								"name TEXT unique not null);";
	
	public static final String DEVICE_TABLE = "device";
	public static final String DEVICE_ID = "_id";
	public static final String DEVICE_NAME = "name";
	public static final String DEVICE_COMMUNITY_FK = "community";
	public static final String DEVICE_ADDRESS = "address";
	public static final String DEVICE_PORT = "port";
	public static final String DEVICE_LOCK = "lock";
	public static final String DEVICE_REPLICANT = "replicant";
	public static final String CREATE_DEVICE_TABLE = "create table device (_id integer primary key autoincrement, " + 
													"name text not null, community integer not null, address text not null, " +
													"port integer, lock boolean not null, replicant boolean not null, " +
													"constraint 'COMMUNITY_FK' foreign key (community) references community(_id), " +
													"constraint 'NAMECOM_UN' unique (name, community) ON CONFLICT IGNORE);";
	
	public static final String SENSOR_TABLE = "sensor";
	public static final String SENSOR_ID = "_id";
	public static final String SENSOR_INTERNAL_ID = "int_id";
	public static final String SENSOR_NAME = "name";
	public static final String SENSOR_UNITS = "units";
	public static final String SENSOR_DIGITAL = "digital";
	public static final String SENSOR_DEVICE_FK = "device";
	public static final String CREATE_SENSOR_TABLE = "create table sensor (_id integer primary key autoincrement, " + 
													"int_id integer not null, " +
													"name text not null, units text not null, digital boolean not null, " + 
													"device integer not null, " + 
													"constraint 'DEVICE_FK' foreign key (device) references device(_id), " + 
													"constraint 'SENDEVINT_UN' unique (int_id, device) ON CONFLICT IGNORE);";
	
	public static final String ACTUATOR_TABLE = "actuator";
	public static final String ACTUATOR_ID = "_id";
	public static final String ACTUATOR_INTERNAL_ID = "int_id";
	public static final String ACTUATOR_NAME = "name";
	public static final String ACTUATOR_MINVALUE = "min_value";
	public static final String ACTUATOR_MAXVALUE = "max_value";
	public static final String ACTUATOR_FEEDBACK = "feedback";
	public static final String ACTUATOR_DEVICE_FK = "device";
	public static final String CREATE_ACTUATOR_TABLE = "create table actuator (_id integer primary key autoincrement, " +
														"int_id integer not null, " +
														"name text not null, min_value integer not null, max_value integer not null, " +
														"feedback boolean not null, device integer not null, " + 
														"constraint 'ACTUATOR_DEVICE_FK' foreign key (device) references device(_id), " +
														"constraint 'ACTDEVINT_UN' unique (int_id, device) ON CONFLICT IGNORE);";
	
	
	private static class DatabaseHelper extends SQLiteOpenHelper {
		
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);			
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			Log.w(LOG_TAG, "Creating community table");
			Log.w(LOG_TAG, CREATE_COMMUNITY_TABLE);
			db.execSQL(CREATE_COMMUNITY_TABLE);
			Log.w(LOG_TAG, "Community table succesfully created");
			
			Log.w(LOG_TAG, "Creating device table");
			Log.w(LOG_TAG, CREATE_DEVICE_TABLE);
			db.execSQL(CREATE_DEVICE_TABLE);
			Log.w(LOG_TAG, "Device table succesfully created");

			Log.w(LOG_TAG, "Creating sensor table");
			Log.w(LOG_TAG, CREATE_SENSOR_TABLE);
			db.execSQL(CREATE_SENSOR_TABLE);
			Log.w(LOG_TAG, "Sensor table succesfully created");
			
			Log.w(LOG_TAG, "Creating actuator table");
			Log.w(LOG_TAG, CREATE_ACTUATOR_TABLE);
			db.execSQL(CREATE_ACTUATOR_TABLE);	
			Log.w(LOG_TAG, "Actuator table succesfully created");
		}
		
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			Log.w(LOG_TAG, "Upgrading database from version " + oldVersion + " to version " + newVersion +
					". Old data will be destroyed.");
			db.execSQL("drop table if exists actuator");
			db.execSQL("drop table if exists sensor");
			db.execSQL("drop table if exists device");
			db.execSQL("drop table if exists community");
			onCreate(db);
		}
		
		/*@Override
		public void onOpen(SQLiteDatabase db) {
			
		}*/	
		
	}
	
	ReplicantDbAdapter(Context ctx) {
		this.ctx = ctx;
	}
	
	/***************************************************************
	 * Open the replicant database. If it can't be opened tries to 
	 * create a new database and if this also fails, then throws an
	 * SQLException.
	 * 
	 * @return A self reference.
	 * @throws SQLException if the database can't be neither opened or
	 * created.
	 */
	public ReplicantDbAdapter open() throws SQLException {
		this.mdbHelper = new DatabaseHelper(ctx);
		database = mdbHelper.getWritableDatabase();
		this.dbStatus = true;
		return this;
	}
	
	/******************************************************
	 * Close the database connection.
	 */
	public void close() {
		this.mdbHelper.close();
		this.dbStatus = false;
	}
	
	/******************************************************
	 * Checks if the database is open or not.
	 */
	public boolean isOpen() {
		return this.dbStatus;
	}
	
	/******************************************************
	 * Creates a new community
	 * @param name New community's name.
	 * @return rowId or -1 if failed.
	 */
	public long createCommunity(String name) {
		// There must be a name.
		int count = 0;
		if (name != null) {
			// We check if the community already exists.
			Cursor c = fetchCommunity(name);
			count = c.getCount();
			c.close();
			if (count == 0) {
				ContentValues val = new ContentValues();
				val.put(COMMUNITY_NAME, name);
				return database.insert(COMMUNITY_TABLE, null, val);
			}
		}	
		return -1;
	}

	/******************************************************
	 * Deletes a community
	 * @param commID Community's identification.
	 * @return true if deleted false otherwise.
	 */	
	public int deleteCommunity(long commId) {
		return database.delete(COMMUNITY_TABLE, COMMUNITY_ID + "=" + commId, null);
	}
	
	/******************************************************
	 * Returns a cursor over the list of all communities.
	 * 
	 * @return cursor over all communities.
	 */	
	public Cursor fetchAllCommunities() {
		return database.query(COMMUNITY_TABLE, 
		new String[] {COMMUNITY_ID, COMMUNITY_NAME}, null, null, null, null, null);
	}
	
	/******************************************************
	 * Returns a cursor positioned at the community that matches 
	 * the given commId
	 * 
	 * @param commId of the community to retrieve.
	 * @return cursor positioned at the matched community, if found.
	 * @throws SQLException if community couldn't be found/retrieved.
	 */		
	public Cursor fetchCommunity(long commId) throws SQLException {

        Cursor c = database.query(true, COMMUNITY_TABLE, new String[] {COMMUNITY_ID,
                    COMMUNITY_NAME}, COMMUNITY_ID + "=" + commId, null,
                    null, null, null, null);
        if (c != null) {
            c.moveToFirst();
        }
        return c;		
	}

	/******************************************************
	 * Returns a cursor positioned at the community that matches 
	 * the given name
	 * 
	 * @param name of the community to retrieve.
	 * @return cursor positioned at the matched community, if found.
	 * @throws SQLException if community couldn't be found/retrieved.
	 */	
	public Cursor fetchCommunity(String name) throws SQLException {
		Cursor c = database.query(COMMUNITY_TABLE, new String [] {COMMUNITY_ID, COMMUNITY_NAME}, 
				COMMUNITY_NAME + "='" + name +"'", null, null, null, null, null);
		if (c != null) {
			c.moveToFirst();
		}
		return c;
	}
	
	/******************************************************
	 * Returns true if there is any community updated, false
	 * on the other way
	 * 
	 * @param id of the community that is going to be updated.
	 * @param name that it's going to be set.
	 * @return the number of rows affected, -1 Otherwise.
	 * @throws SQLException if community couldn't be found/retrieved.
	 */	
	public int updateCommunity(long id, String name) {
		// There must be a name.
		int count = 0;
		if (name != null) {
			Cursor c = fetchCommunity(name);
			count = c.getCount();
			c.close();
			if (count == 0) {
				ContentValues args = new ContentValues();
				args.put(COMMUNITY_ID, id);
				args.put(COMMUNITY_NAME, name);
				return database.update(COMMUNITY_TABLE, args, COMMUNITY_ID + "=" + id, null);
			}
		}
		return -1;
	}

	/******************************************************
	 * Creates a new device attached to a community
	 * @param name New device's name.
	 * @param commId Device's community.
	 * @param address Device's address it can be an IPv4, IPv6 a file descriptor
	 * @param port Device's port, or -1 if there isn't any port.
	 * @param lock Device's locking state. 
	 * or whatever
	 * @return rowId or -1 if failed.
	 */
	public long createDevice(String name, long commId, String address, long port, boolean lock) {
		ContentValues val = new ContentValues();
		val.put(DEVICE_NAME, name);
		val.put(DEVICE_COMMUNITY_FK, commId);
		val.put(DEVICE_ADDRESS, address);
		if (port >= 0) {
			val.put(DEVICE_PORT, port);
		}
		val.put(DEVICE_LOCK, lock);
		val.put(DEVICE_REPLICANT, false);
		return database.insert(DEVICE_TABLE, null, val);		
		
	}
	
	/******************************************************
	 * Removes a device.
	 * @param devId New device's id.
	 * @return true if deleted, false otherwise.
	 */
	public int deleteDevice(long devId) {
		return database.delete(DEVICE_TABLE, DEVICE_ID + "=" + String.valueOf(devId), null);
	}
	
	/******************************************************
	 * Returns a Cursor with all devices.
	 * @return a Cursor with all devices.
	 */
	public Cursor fetchAllDevices() {
		return database.query(DEVICE_TABLE, 
				new String[] {DEVICE_ID, DEVICE_NAME, DEVICE_ADDRESS, DEVICE_PORT, DEVICE_LOCK, DEVICE_REPLICANT, DEVICE_COMMUNITY_FK}, null, null, null, null, null);		
	}
	
	/******************************************************
	 * Returns a Cursor with all devices attached to a 
	 * specific community.
	 * @param commId Community of the devices.
	 * @return a Cursor with all devices attached to commId community.
	 */ 	
	public Cursor fetchDevicesByCommunity(long commId) {
		return database.query(DEVICE_TABLE, 
				new String[] {DEVICE_ID, DEVICE_NAME, DEVICE_ADDRESS, DEVICE_PORT, DEVICE_LOCK, DEVICE_REPLICANT, DEVICE_COMMUNITY_FK}, 
				DEVICE_COMMUNITY_FK + "=" + String.valueOf(commId), null, null, null, DEVICE_NAME);
	}
	
	/******************************************************
	 * Returns a Cursor with all devices named as the argument passed in the community.
	 * 
	 * @param commId Community of the devices.
	 * @param name Name of the device.
	 * @return a Cursor with all devices attached to commId community with name name.
	 */ 	
	public Cursor fetchDevicesByName(long commId, String name) {
		return database.query(DEVICE_TABLE, 
				new String[] {DEVICE_ID, DEVICE_NAME, DEVICE_ADDRESS, DEVICE_PORT, DEVICE_LOCK, DEVICE_REPLICANT, DEVICE_COMMUNITY_FK}, 
				DEVICE_COMMUNITY_FK + "=" + String.valueOf(commId) + " AND " + DEVICE_NAME + "='" +
				name + "'", null, null, null, DEVICE_NAME);
	}
	
	/******************************************************
	 * Returns a Cursor with an specific device defined by devId
	 * @param devId Device Id
	 * @return a Cursor with the device defined by devId.
	 */ 		
	public Cursor fetchDevice(long devId) {
		return database.query(DEVICE_TABLE, 
				new String[] {DEVICE_ID, DEVICE_NAME, DEVICE_ADDRESS, DEVICE_PORT, DEVICE_LOCK, DEVICE_REPLICANT, DEVICE_COMMUNITY_FK}, 
				DEVICE_ID + "=" + String.valueOf(devId), null, null, null, null);
		
	}
	
	/***********************************************************
	 * Updates the specific device 
	 * 
	 * @param devId Id of the device.
	 * @param name	Name of the device.
	 * @param devAddress Address of the device.
	 * @param port	Port of the device or -1 if there isn't one.
	 * @param lock Lock status.
	 * @param replicant Shows if the device is replicant compliant or not.
	 * @param commId Community id of the device.
	 * @return The number of devices updated or -1.
	 */	
	public int updateDevice(long devId, String name, String devAddress, long port, boolean lock, boolean replicant, long commId) {

		ContentValues args = new ContentValues();
		args.put(DEVICE_ID, devId);
		args.put(DEVICE_NAME, name);
		args.put(DEVICE_ADDRESS, devAddress);
		if (port >= 0) {
			args.put(DEVICE_PORT, port);
		}
		args.put(DEVICE_LOCK, lock);
		args.put(DEVICE_REPLICANT, replicant);
		args.put(DEVICE_COMMUNITY_FK, commId);
		return database.update(DEVICE_TABLE, args, DEVICE_ID + "=" + devId, null);
	}
	
	/******************************************************
	 * Creates a new sensor attached to a device
	 * @param name New sensor's name.
	 * @param units Units of the sensor measures.
	 * @param isDigital Indicates if the sensor is analog or digital.
	 * @param devId Represents a foreign key to the device where sensor is attached.
	 * @return rowId or -1 if failed.
	 */
	public long createSensor(String name, String units, boolean isDigital, long devId, long int_id) {
		ContentValues val = new ContentValues();
		val.put(SENSOR_NAME, name);
		val.put(SENSOR_UNITS, units);
		val.put(SENSOR_DIGITAL, isDigital);
		val.put(SENSOR_DEVICE_FK, devId);
		val.put(SENSOR_INTERNAL_ID, int_id);
		return database.insert(SENSOR_TABLE, null, val);	
		
	}
	
	/******************************************************
	 * Removes a sensor.
	 * @param sensorId Sensor's id.
	 * @return true if deleted, false otherwise.
	 */
	public int deleteSensor(long sensId) {
		return database.delete(SENSOR_TABLE, SENSOR_ID + "=" + String.valueOf(sensId), null);
	}
	
	/*******************************************************
	 * Fetches an specific Sensor.
	 * @param sensId Sensor's id.
	 * @return A Cursor with the actuator.
	 */
	public Cursor fetchSensor(long sensId) {
		return database.query(SENSOR_TABLE, 
				new String[] {SENSOR_ID, SENSOR_NAME, SENSOR_UNITS, SENSOR_DIGITAL, SENSOR_DEVICE_FK, SENSOR_INTERNAL_ID}, 
				SENSOR_ID + "=" + String.valueOf(sensId), null, null, null, null);
	}
	
	
	/*******************************************************
	 * Fetches all sensors attached to a device.
	 * @param devId Device's id.
	 * @return A Cursor with all the sensors attached to an specific device.
	 */
	public Cursor fetchSensorByDevice(long devId) {
		return database.query(SENSOR_TABLE, 
				new String[] {SENSOR_ID, SENSOR_NAME, SENSOR_UNITS, SENSOR_DIGITAL, SENSOR_DEVICE_FK, SENSOR_INTERNAL_ID}, 
				SENSOR_DEVICE_FK + "=" + String.valueOf(devId), null, null, null, null);
	}
	
	
	
	/******************************************************
	 * Creates a new sensor attached to a device
	 * @param name New actuator's name.
	 * @param minValue Minimum value that can be sent to this actuator.
	 * @param maxValue Maximum value that can be sent to this actuator.
	 * @param hasFeedback Shows if the actuator is a closed loop one or not.
	 * @param devId Represents a foreign key to the device where actuator is attached.
	 * @return rowId or -1 if failed.
	 */
	public long createActuator(String name, long minValue, long maxValue, boolean hasFeedback, long devId, long int_id) {
		ContentValues val = new ContentValues();
		val.put(ACTUATOR_NAME, name);
		val.put(ACTUATOR_MINVALUE, minValue);
		val.put(ACTUATOR_MAXVALUE, maxValue);
		val.put(ACTUATOR_FEEDBACK, hasFeedback);
		val.put(ACTUATOR_DEVICE_FK, devId);
		val.put(ACTUATOR_INTERNAL_ID, int_id);
		return database.insert(ACTUATOR_TABLE, null, val);
	}
	
	/******************************************************
	 * Removes an actuator.
	 * @param actId Actuator's id.
	 * @return true if deleted, false otherwise.
	 */
	public int deleteActuator(long actId) {
		return database.delete(ACTUATOR_TABLE, ACTUATOR_ID + "=" + String.valueOf(actId), null);
	}
	
	/*******************************************************
	 * Fetches an specific Actuator.
	 * @param actId Actuator's id.
	 * @return A Cursor with the actuator.
	 */
	public Cursor fetchActuator(long actId) {
		return database.query(ACTUATOR_TABLE, 
				new String[] {ACTUATOR_ID, ACTUATOR_NAME, ACTUATOR_MINVALUE, ACTUATOR_MAXVALUE, ACTUATOR_FEEDBACK, ACTUATOR_DEVICE_FK, ACTUATOR_INTERNAL_ID}, 
				ACTUATOR_ID + "=" + String.valueOf(actId), null, null, null, null);
	}
	
	/*******************************************************
	 * Fetches all actuators attached to a device.
	 * @param devId Device's id.
	 * @return A Cursor with all actuators attached to an specific device.
	 */
	public Cursor fetchActuatorByDevice(long devId) {
		return database.query(ACTUATOR_TABLE, 
				new String[] {ACTUATOR_ID, ACTUATOR_NAME, ACTUATOR_MINVALUE, ACTUATOR_MAXVALUE, ACTUATOR_FEEDBACK, ACTUATOR_DEVICE_FK, ACTUATOR_INTERNAL_ID}, 
				ACTUATOR_DEVICE_FK + "=" + String.valueOf(devId), null, null, null, null);
	}
	
}
